package haha.life.normal;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;


/**
 * Excel导入导出
 *
 * @author 小道仙
 * @date 2020年8月22日
 */
public class ExcelUtils {
    public final String XLSX = ".xlsx";
    public final String XLS = ".xls";

    /**
     * 返回Excel
      * @param response
     * @param title
     * @param wb
     * @throws Exception
     */
    public static void setExcelResponse(HttpServletResponse response, String title, Workbook wb) throws Exception {
        response.reset();
        response.setContentType("application/octet-stream; charset=utf-8");
        response.setHeader("Access-Control-Allow-Origin", "*");
        response.setHeader("Content-disposition", "attachment;filename=" + java.net.URLEncoder.encode(title, "UTF-8"));
        wb.write(response.getOutputStream());
        response.getOutputStream().close();
        wb.close();
    }

    /**
     * Excel 导出工作簿
     *
     * @param header    表头
     * @param keys      map的key值
     * @param content   内容数据
     * @param sheetName sheet名
     */
    public static Workbook exportExcel(String[] header, String[] keys, List<Map<String, Object>> content, String sheetName) throws Exception {
        Workbook wb = new SXSSFWorkbook(1000);
        Sheet sheet = wb.createSheet(sheetName);
        Row row = sheet.createRow(0);
        // 行高
        row.setHeight((short) 700);
        // 列宽
        for (int i = 0; i < header.length; i++) {
            sheet.setColumnWidth(i, 20 * 256);
        }
        for (int i = 0; i < header.length; i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(header[i]);
            cell.setCellStyle(HeaderStyle(wb));
        }

        CellStyle cellStyle = contentStyle(wb);
        for (int i = 0; i < content.size(); i++) {
            Map<String, Object> map = content.get(i);
            row = sheet.createRow((int) i + 1);
            row.setHeight((short) 500);
            for (int j = 0; j < keys.length; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(map.get(keys[j]) == null ? "" : map.get(keys[j]).toString());
                cell.setCellStyle(cellStyle);
            }
        }
        return  wb;
    }

    /**
     * Excel 导入
     *
     * @param keys 数据顺序
     */
    public static List<Map<String, Object>> importExcel(String fileName ,InputStream inputStream, String[] keys) throws Exception {
        Workbook wb = null;
//        String fileName = file.getOriginalFilename();
        if (fileName.endsWith("xls")) {
            POIFSFileSystem pois = new POIFSFileSystem(inputStream);
            wb = new HSSFWorkbook(pois);
        } else if (fileName.endsWith("xlsx")) {
            wb = new XSSFWorkbook(inputStream);
        }
        Sheet sheet = wb.getSheetAt(0);
        int rowCount = sheet.getPhysicalNumberOfRows();
        if (sheet.getRow(1).getPhysicalNumberOfCells() != keys.length) {
            throw new RuntimeException("导入的Excel和模板的列不匹配");
        }
        List<Map<String, Object>> result = new ArrayList<>();
        for (int i = 0; i < rowCount - 1; i++) {
            Row row = sheet.getRow(i + 1);
            Map<String, Object> tmp = new HashMap<>();
            for (int j = 0; j < keys.length; j++) {
                Cell cell = row.getCell(j);
                // 把类型转行Spring
//                cell.setCellType(CellType.STRING);
                tmp.put(keys[j], cell.getStringCellValue());
            }
            result.add(tmp);
        }
        return result;
    }

    /**
     * 表头样式
     */
    private static CellStyle HeaderStyle(Workbook wb) {
        Font font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 11);
        CellStyle cellStyle = commonStyle(wb);
        cellStyle.setFont(font);
        return cellStyle;
    }

    /**
     * 内容样式
     */
    private static CellStyle contentStyle(Workbook wb) {
        Font font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 10);
        CellStyle cellStyle = commonStyle(wb);
        cellStyle.setFont(font);
        return cellStyle;
    }

    /**
     * 公共样式
     */
    private static CellStyle commonStyle(Workbook wb) {
        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setWrapText(true);// 自动换行
        return style;
    }
    /**
     * 获取Excel文件（.xls和.xlsx都支持）
     *
     * @param file
     * @return 解析excle后的Json数据
     * @throws IOException
     * @throws FileNotFoundException
     * @throws InvalidFormatException
     */
    public JSONArray readExcel(File file) throws Exception {
        int res = checkFile(file);

        if (res == 0) {
            System.out.println("File not found");
        } else if (res == 1) {
            return readXLSX(file);
        } else if (res == 2) {
            return readXLS(file);
        }
        JSONArray array = new JSONArray();
        return array;
    }

    /**
     * 判断File文件的类型
     *
     * @param file 传入的文件
     * @return 0-文件为空，1-XLSX文件，2-XLS文件，3-其他文件
     */
    public int checkFile(File file) {
        if (file == null) {
            return 0;
        }
        String flieName = file.getName();
        if (flieName.endsWith(XLSX)) {
            return 1;
        }
        if (flieName.endsWith(XLS)) {
            return 2;
        }
        return 3;
    }

    /**
     * 读取XLSX文件
     *
     * @param file
     * @return
     * @throws IOException
     * @throws InvalidFormatException
     */
    public JSONArray readXLSX(File file) throws InvalidFormatException, IOException {
        Workbook book = new XSSFWorkbook(file);
        Sheet sheet = book.getSheetAt(0);
        return read(sheet, book);
    }

    /**
     * 读取XLS文件
     *
     * @param file
     * @return
     * @throws IOException
     * @throws FileNotFoundException
     */
    public JSONArray readXLS(File file) throws FileNotFoundException, IOException {
        POIFSFileSystem poifsFileSystem = new POIFSFileSystem(new FileInputStream(file));
        Workbook book = new HSSFWorkbook(poifsFileSystem);
        Sheet sheet = book.getSheetAt(0);
        return read(sheet, book);
    }

    /**
     * 解析数据
     *
     * @param sheet 表格sheet对象
     * @param book  用于流关闭
     * @return
     * @throws IOException
     */
    public JSONArray read(Sheet sheet, Workbook book) throws IOException {
        int rowStart = sheet.getFirstRowNum();    // 首行下标
        int rowEnd = sheet.getLastRowNum();    // 尾行下标
        // 如果首行与尾行相同，表明只有一行，直接返回空数组
        if (rowStart == rowEnd) {
            book.close();
            return new JSONArray();
        }
        // 获取第一行JSON对象键
        Row firstRow = sheet.getRow(rowStart);
        int cellStart = firstRow.getFirstCellNum();
        int cellEnd = firstRow.getLastCellNum();
        Map<Integer, String> keyMap = new HashMap<Integer, String>();
        for (int j = cellStart; j < cellEnd; j++) {
            keyMap.put(j, getValue(firstRow.getCell(j), rowStart, j, book, true));
        }
        // 获取每行JSON对象的值
        JSONArray array = new JSONArray();
        for (int i = rowStart + 1; i <= rowEnd; i++) {
            Row eachRow = sheet.getRow(i);
            JSONObject obj = new JSONObject();
            StringBuffer sb = new StringBuffer();
            for (int k = cellStart; k < cellEnd; k++) {
                if (eachRow != null) {
                    String val = getValue(eachRow.getCell(k), i, k, book, false);
                    sb.append(val);        // 所有数据添加到里面，用于判断该行是否为空
                    obj.put(keyMap.get(k), val);
                }
            }
            if (sb.toString().length() > 0) {
                array.add(obj);
            }
        }
        book.close();
        return array;
    }

    /**
     * 获取每个单元格的数据
     *
     * @param cell   单元格对象
     * @param rowNum 第几行
     * @param index  该行第几个
     * @param book   主要用于关闭流
     * @param isKey  是否为键：true-是，false-不是。 如果解析Json键，值为空时报错；如果不是Json键，值为空不报错
     * @return
     * @throws IOException
     */
    public String getValue(Cell cell, int rowNum, int index, Workbook book, boolean isKey) throws IOException {

        // 空白或空
        if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) {
            if (isKey) {
                book.close();
                throw new NullPointerException(String.format("the key on row %s index %s is null ", ++rowNum, ++index));
            } else {
                return "";
            }
        }

        // 0. 数字 类型
        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                Date date = cell.getDateCellValue();
                DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                return df.format(date);
            }
            String val = cell.getNumericCellValue() + "";
            val = val.toUpperCase();
            if (val.contains("E")) {
                val = val.split("E")[0].replace(".", "");
            }
            return val;
        }

        // 1. String类型
        if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
            String val = cell.getStringCellValue();
            if (val == null || val.trim().length() == 0) {
                if (book != null) {
                    book.close();
                }
                return "";
            }
            return val.trim();
        }

        // 2. 公式 CELL_TYPE_FORMULA
        if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
            return cell.getStringCellValue();
        }

        // 4. 布尔值 CELL_TYPE_BOOLEAN
        if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            return cell.getBooleanCellValue() + "";
        }
        // 5.	错误 CELL_TYPE_ERROR
        return "";
    }

}

